package com.tau.egonetwork.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.tau.egonetwork.common.Log;
import com.tau.egonetwork.exceptions.ExcelCastException;
import com.tau.egonetwork.facebook.FacebookMainProfile;
import com.tau.egonetwork.linkedin.LinkedinMainProfile;
import com.tau.egonetwork.profiles.Group;
import com.tau.egonetwork.profiles.MainProfile;
import com.tau.egonetwork.profiles.Profile;
import com.tau.egonetwork.profiles.ProfileFile;
import com.tau.egonetwork.profiles.ProfileFile.SocialNetworks;

public class ExcelReadUtils {

	private static final String PROFILE_FACEBOOK = "Facebook";
	private static final String PROFILE_LINKEDIN = "Linkedin";
	
	private static IExcel excelHandler;
		
	public static void readProfile(String filename, IExcelRead excelRead) {

		Log.writeLineToLog("ExcelReadUtils.readProfile with " + filename);
		
		//check file really does exist:
		File excelFile = new File(filename);
		if (!excelFile.exists())
			throw new ExcelCastException("File " + filename + " does not exist.");
		
		Workbook w;
		
		try {
			//read workbook from file:
			Log.writeLineToLog("Reading workbook...");
			
			//Apache POI library works differently for xls files (HSSF)
			//and xlsx files (XSSF) - work with them accordingly:
			if (filename.endsWith(".xls")) {
				w = new HSSFWorkbook(new FileInputStream(excelFile));
			} else if (filename.endsWith(".xlsx")) {
				w = new XSSFWorkbook(new FileInputStream(excelFile));
			} else {
				throw new ExcelCastException("Illegal file extension - can only accept xls and xlsx files");
			}
			
			//read first sheet - friends and connections:
			MainProfile<?> profile = readConnectionsSheet(w, filename, excelRead);

			//read second sheet - groups:
			readGroupsDetails(w, profile);
					
			//save profile:
			SocialNetworks net = null;
			if (profile instanceof FacebookMainProfile)
				net = SocialNetworks.Facebook;
			else if (profile instanceof LinkedinMainProfile)
				net = SocialNetworks.Linkedin;
			ProfileFile.saveToFile(net, profile);
			
			excelRead.onFinish(profile);
			
		} catch (IOException e) {
			throw new ExcelCastException("Unknown error occured while trying to read excel file.", e);	
		}
	}
	
	private static MainProfile<?> readConnectionsSheet(Workbook w, String filename, IExcelRead excelRead) {
		
		Log.writeLineToLog("readConnectionsSheet() called");
		
		Sheet sheet = w.getSheetAt(0);

		Iterator<Row> rows = sheet.iterator();
		
		if (!rows.hasNext())
			throw new ExcelCastException("first sheet must contain any rows / columns");
		
		//create profile according to type in (0,0) cell:
		
		//first extract name from filename:
		Log.writeLineToLog("extracting profile name from file name");
		
		int lastSeperator = filename.lastIndexOf(File.separator);
		int lastDot = filename.lastIndexOf(".xls");
	
		if (lastDot == -1 || lastDot <= lastSeperator) {
			throw new ExcelCastException("illegal filename");
		}
		
		String profileName = filename.substring(lastSeperator + 1, lastDot);
		
		Log.writeLineToLog("Profile name: " + profileName);
		
		MainProfile<?> profile = null;
		
		Row firstRow = rows.next();
		Cell zeroCell = firstRow.getCell(0);
		if (zeroCell.getCellType() != Cell.CELL_TYPE_STRING)
			throw new ExcelCastException("Cell (0,0) has to be a string.");
		
		String type = zeroCell.getStringCellValue();
		
		if (type.equals(PROFILE_FACEBOOK)) {
			excelHandler = new FacebookExcel();
			excelRead.onNetworkDetected(SocialNetworks.Facebook);
		} else if (type.equals(PROFILE_LINKEDIN)) {
			excelHandler = new LinkedinExcel();
			excelRead.onNetworkDetected(SocialNetworks.Linkedin);
		} else {
			throw new ExcelCastException("Cell (0,0) must contain a valid network (Facebook or Linkedin)");
		}

		excelRead.onProfileName(profileName);
		profile = excelHandler.createMainProfile(profileName);
		
		//get friends, create them and check rows match columns:
		profile.createFriendsList();
		
		Log.writeLineToLog("read friends");
		
		Iterator<Cell> firstRowFriends = firstRow.iterator();
		//we already know there's a first column - skip it (network value):
		firstRowFriends.next();
		
		//go over the rest of the row (first check there is a row) :
		if (!firstRowFriends.hasNext()) {
			//there are no friend (empty boring profile), check that columns
			//reflect this as well:
			if (rows.hasNext()) {
				//there are friends listed in column 0 - bad excel file:
				throw new ExcelCastException("first row does not contain friends, who are present in first column.");
			}
			//otherwise - boring excel file, but still okay
		}
		
		int rowNumber = 1;
		while (firstRowFriends.hasNext()) {
			
			if (!rows.hasNext()) {
				throw new ExcelCastException("Number of rows does not match number of columns.");
			}
			
			Cell columnCell = rows.next().getCell(0);
			Cell rowCell = firstRowFriends.next();
			
			if (rowCell.getCellType() != Cell.CELL_TYPE_STRING)
				throw new ExcelCastException("Friend in first row at cell " + rowNumber + " is not a string.");

			if (columnCell.getCellType() != Cell.CELL_TYPE_STRING)
				throw new ExcelCastException("Friend in first column at row " + rowNumber + " is not a string.");
			
			if (!columnCell.getStringCellValue().equals(rowCell.getStringCellValue()))
				throw new ExcelCastException("mismatched name between row and column " + rowNumber);
			
			excelHandler.addToFriendsList(profile,
					excelHandler.createProfile(columnCell.getStringCellValue()));
			
			rowNumber++;
		}
		
		//we've read all rows, check there are no more columns:
		if (rows.hasNext()) {
			throw new ExcelCastException("Cannot process excel file because there are more rows than columns.");
		}
		
		//create connections between profiles:
		Log.writeLineToLog("get connections");
		
		//reset rows:
		rows = sheet.iterator();
		//move over first row:
		rows.next();
		
		//scan over friendship and build actual matrix (will make validity checking easier):
		int[][] mat = new int[rowNumber][rowNumber];
		for (int i = 0; rows.hasNext(); i++) {
			//we now know for sure numOfRows == numOfColumns!
			Iterator<Cell> cells = rows.next().iterator();
			//skip first column (names column):
			cells.next();
			//read and fill matrix:
			for (int j = 0; cells.hasNext(); j++) {
				
				Cell cell = cells.next();
				boolean badCell = false;
				if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC)
					badCell = true;
				
				double val = 0;
				if (!badCell) {
					val = cell.getNumericCellValue();
					if (val != 0 && val != 1)
						badCell = true;
				}
				
				if (badCell)
					throw new ExcelCastException("Connection between two people at [" + (i+2) + ", " + (j+2) + "] must be either 1 or 0");

				mat[i][j] = (int)val;
			}
		}
		
		//check matrix is legal and create profile accordingly:
		for (int i = 0; i < rowNumber; i++) {
			//scan columns from rows i, since you've read
			//and checked before:
			for (int j = i; j < rowNumber; j++) {
				
				if (i == j)
					continue;
				
				if (mat[i][j] != mat[j][i]) {
					throw new ExcelCastException("Connection must be bi-directional between row " + (i+2) + " and column " + (j+2));
				}
				
				if (mat[i][j] == 1) {
					excelHandler.createConnection(
							profile.getFriendsList().get(i),
							profile.getFriendsList().get(j));
				}
				
			}
		}
		
		return profile;
	}
	
	private static void readGroupsDetails(Workbook w, MainProfile<?> profile) {
		
		Log.writeLineToLog("readGroupsDetails() called");
		
		if (w.getNumberOfSheets() != 2)
			return;
		
		Log.writeLineToLog("Second sheet exists");
		
		Sheet sheet = w.getSheetAt(1);
		
		Iterator<Row> rows = sheet.iterator();
		
		if (!rows.hasNext()) {
			throw new ExcelCastException("Groups sheet must contain either rows or columns, if exists");
		}
		
		profile.createGroupsList();
		
		int rowNumber = 1;
		while (rows.hasNext()) {
			
			Row row = rows.next();
			
			//read group name from first row:
			Iterator<Cell> rowCells = row.cellIterator();
			if (!rowCells.hasNext())
				throw new ExcelCastException("row " + rowNumber + " must have cells");
			
			Cell groupNameCell = rowCells.next();
			if (groupNameCell.getCellType() != Cell.CELL_TYPE_STRING)
				throw new ExcelCastException("Group name must be a string (at row " + rowNumber + ")");
						
			Group<?> g = excelHandler.createGroup(groupNameCell.getStringCellValue());
			
			//go over rest of row:
			while(rowCells.hasNext()) {
				
				Cell groupMember = rowCells.next();
				if (groupMember.getCellType() != Cell.CELL_TYPE_STRING)
					throw new ExcelCastException("Illegal group member at row " + rowNumber + " - must be a string.");
				
				String groupMemberName = groupMember.getStringCellValue();
				Profile<?> p = profile.getFriendsList().getByName(groupMemberName);
				
				if (p == null)
					throw new ExcelCastException(groupMemberName + " in groups sheet does not exist in first sheet");
				
				excelHandler.addToGroup(g, p);
				
			}
			
			excelHandler.addGroup(g, profile);
			rowNumber++;
			
		}
		
	}
	
}
